#!/usr/bin/perl

######################################################################
#
# mdb2xls.pl
#
# $Id: mdb2xls.pl 148 2014-01-29 12:10:08Z drj826@gmail.com $
#
# Convert PPARES membership database (CSV format) to an Excel
# spreadsheet.
#
#---------------------------------------------------------------------
# USAGE (execution)
#
#     perl mdb2xls.pl --skip 1 --order 10 -- roster.csv
#
#         --order   | -o => order by values in specified column
#         --skip    | -s => comma separated list of lines to skip
#
######################################################################

use strict;
use Text::CSV;
use Getopt::Long qw(:config no_ignore_case);

use Excel::Writer::XLSX;
use Excel::Writer::XLSX::Worksheet;

my $order;                              # column on which to sort records
my @skip;                               # line numbers to skip

GetOptions
  (
   "order|o=s" => \$order,
   "skip|s=s"  => \@skip,
  );

@skip = split(',',join(',',@skip));

my %skip_line;                          # lines to skip
foreach my $line (@skip) {
  $skip_line{$line} = 1;
}

my $csvfile = shift;

if ( not -r $csvfile )
  {
    die "CSV file not readable $csvfile";
  }

my $data    = read_csv_file($csvfile);
my $xlsfile = 'private/xls/ppares-roster.xlsx';
my $wb      = Excel::Writer::XLSX->new($xlsfile);
my $mws     = $wb->add_worksheet('members');
my $green   = $wb->add_format(bg_color=>'green',align=>'center',valign=>'top');
my $red     = $wb->add_format(bg_color=>'red',align=>'center',valign=>'top');
my $left    = $wb->add_format(align=>'left',text_wrap=>1,valign=>'top');
my $center  = $wb->add_format(align=>'center',valign=>'top');
my $head    = $wb->add_format(bold=>1,align=>'left',text_wrap=>1,valign=>'top');

my $c = 0;                              # column number
my $r = 0;                              # row number

foreach my $column (@{ get_column_list() })
  {
    my $heading = $column->[0];
    my $width   = $column->[1];
    my $format  = $column->[2];

    $mws->set_column($c,$c,$width,$format);
    $mws->write($r,$c,$heading,$head);

    ++ $c                               # increment column number
  }

++ $r;                                  # increment row number

foreach my $member (@{ get_member_list() })
  {
    add_row($member);
    ++ $r;                              # increment row number
  }

$mws->freeze_panes(1,0);
$mws->set_zoom(75);

######################################################################

sub get_column_list {

  my $left   = shift;
  my $center = shift;

  return
    [
     # heading                  width  format
     # -----------------------  -----  -------
     ['callsign',                  10, $left   ],
     ['lastname',                  12, $left   ],
     ['firstname',                 17, $left   ],
     ['middlename',                12, $left   ],
     ['license class',             15, $left   ],
     ['ID',                         3, $center ],

     ['phone',                     30, $left   ],
     ['email',                     30, $left   ],
     ['SMS',                       30, $left   ],
     ['address',                   22, $left   ],
     ['county',                    20, $left   ],
     ['district',                   7, $left   ],

     ['notify of emergencies',     12, $center ],
     ['notify of ARES events',     12, $center ],
     ['notify of exercises',       12, $center ],
     ['notify of WX events',       12, $center ],
     ['notify of PS events',       12, $center ],

     ['available 24/7',            12, $center ],
     ['available M-F daytime',     12, $center ],
     ['available M-F evening',     12, $center ],
     ['available S-S daytime',     12, $center ],
     ['available S-S evening',     12, $center ],

     ['WX available 24/7',         12, $center ],
     ['WX available M-F daytime',  12, $center ],
     ['WX available M-F evening',  12, $center ],
     ['WX available S-S daytime',  12, $center ],
     ['WX available S-S evening',  12, $center ],

     ['EC-001',                    12, $center ],
     ['EC-016',                    12, $center ],
     ['ICS-100',                   12, $center ],
     ['ICS-200',                   12, $center ],
     ['ICS-700',                   12, $center ],
     ['ICS-800',                   12, $center ],

     ['OES',                       12, $center ],
     ['CPR',                       12, $center ],
     ['MEAT',                      12, $center ],
     ['Skywarn',                   12, $center ],
     ['Military ID',               12, $center ],
     ['CBI',                       12, $center ],

     ['VHF FM fixed',              12, $center ],
     ['VHF FM mobile',             12, $center ],
     ['UHF FM fixed',              12, $center ],
     ['UHF FM mobile',             12, $center ],
     ['2m SSB',                    12, $center ],
     ['FM 220',                    12, $center ],

     ['HF mobile',                 12, $center ],
     ['HF portable',               12, $center ],

     ['digital fixed',             12, $center ],
     ['digital portable',          12, $center ],
     ['24/7 packet station',       12, $center ],
     ['portable packet',           12, $center ],
     ['digipeater',                12, $center ],
     ['packet node',               12, $center ],
     ['airmail',                   12, $center ],
     ['APRS',                      12, $center ],
     ['amateur television',        12, $center ],

     ['mag mount antenna',         12, $center ],
     ['dualband mag mount',        12, $center ],
     ['portable HF antenna',       12, $center ],

     ['home WX station',           12, $center ],
     ['kestrel',                   12, $center ],
     ['GPS',                       12, $center ],
     ['FWD',                       12, $center ],

     ['portable generator',        12, $center ],
     ['emergency power',           12, $center ],

     ['last update',               20, $left   ],
     ['initial registration',      20, $left   ],
    ];
}

######################################################################

sub add_row {

  my $record = shift;
  my $c      = 0;                       # column number
  my $d      = {};                      # data hash
  my $f      = {};                      # format hash

  $d->{phone}        = get_phone($record);
  $d->{email}        = get_email($record);
  $d->{sms}          = get_sms($record);
  $d->{address}      = get_address($record);

  $d->{notify_wx}    = yes_or_no($record,51);
  $f->{notify_wx}    = red_or_green($record,51);

  $d->{notify_ares}  = yes_or_no($record,52);
  $f->{notify_ares}  = red_or_green($record,52);

  $d->{notify_exer}  = yes_or_no($record,53);
  $f->{notify_exer}  = red_or_green($record,53);

  $d->{notify_pse}   = yes_or_no($record,54);
  $f->{notify_pse}   = red_or_green($record,54);

  $d->{notify_emerg} = yes_or_no($record,55);
  $f->{notify_emerg} = red_or_green($record,55);

  $d->{avail_247}    = yes_or_no($record,56);
  $f->{avail_247}    = red_or_green($record,56);

  $d->{avail_mfd}    = yes_or_no($record,57);
  $f->{avail_mfd}    = red_or_green($record,57);

  $d->{avail_mfe}    = yes_or_no($record,58);
  $f->{avail_mfe}    = red_or_green($record,58);

  $d->{avail_ssd}    = yes_or_no($record,59);
  $f->{avail_ssd}    = red_or_green($record,59);

  $d->{avail_sse}    = yes_or_no($record,60);
  $f->{avail_sse}    = red_or_green($record,60);

  $d->{wxavail_247}  = yes_or_no($record,61);
  $f->{wxavail_247}  = red_or_green($record,61);

  $d->{wxavail_mfd}  = yes_or_no($record,62);
  $f->{wxavail_mfd}  = red_or_green($record,62);

  $d->{wxavail_mfe}  = yes_or_no($record,63);
  $f->{wxavail_mfe}  = red_or_green($record,63);

  $d->{wxavail_ssd}  = yes_or_no($record,64);
  $f->{wxavail_ssd}  = red_or_green($record,64);

  $d->{wxavail_sse}  = yes_or_no($record,65);
  $f->{wxavail_sse}  = red_or_green($record,65);

  $d->{ec001}        = date_of($record,66,67);
  $f->{ec001}        = red_or_green($record,66);

  $d->{ec016}        = date_of($record,68,69);
  $f->{ec016}        = red_or_green($record,68);

  $d->{ics100}       = date_of($record,74,75);
  $f->{ics100}       = red_or_green($record,74);

  $d->{ics200}       = date_of($record,76,77);
  $f->{ics200}       = red_or_green($record,76);

  $d->{ics700}       = date_of($record,78,79);
  $f->{ics700}       = red_or_green($record,78);

  $d->{ics800}       = date_of($record,80,81);
  $f->{ics800}       = red_or_green($record,80);

  $d->{oes}          = date_of($record,82,83);
  $f->{oes}          = red_or_green($record,82);

  $d->{cpr}          = date_of($record,84,85);
  $f->{cpr}          = red_or_green($record,84);

  $d->{meat}         = date_of($record,86,87);
  $f->{meat}         = red_or_green($record,86);

  $d->{skywarn}      = date_of($record,88,89);
  $f->{skywarn}      = red_or_green($record,88);

  $d->{milid}        = yes_or_no($record,90);
  $f->{milid}        = red_or_green($record,90);

  $d->{hf_mobile}    = yes_or_no($record,93);
  $f->{hf_mobile}    = red_or_green($record,93);

  $d->{hf_portable}  = yes_or_no($record,94);
  $f->{hf_portable}  = red_or_green($record,94);

  $d->{dig_fixed}    = yes_or_no($record,95);
  $f->{dig_fixed}    = red_or_green($record,95);

  $d->{dig_portable} = yes_or_no($record,96);
  $f->{dig_portable} = red_or_green($record,96);

  $d->{vhf_fixed}    = yes_or_no($record,97);
  $f->{vhf_fixed}    = red_or_green($record,97);

  $d->{vhf_mobile}   = yes_or_no($record,98);
  $f->{vhf_mobile}   = red_or_green($record,98);

  $d->{uhf_fixed}    = yes_or_no($record,99);
  $f->{uhf_fixed}    = red_or_green($record,99);

  $d->{uhf_mobile}   = yes_or_no($record,100);
  $f->{uhf_mobile}   = red_or_green($record,100);

  $d->{ssb_2m}       = yes_or_no($record,101);
  $f->{ssb_2m}       = red_or_green($record,101);

  $d->{fm_220}       = yes_or_no($record,102);
  $f->{fm_220}       = red_or_green($record,102);

  $d->{aprs}         = yes_or_no($record,103);
  $f->{aprs}         = red_or_green($record,103);

  $d->{gps}          = yes_or_no($record,104);
  $f->{gps}          = red_or_green($record,104);

  $d->{atv}          = yes_or_no($record,105);
  $f->{atv}          = red_or_green($record,105);

  $d->{kestrel}      = yes_or_no($record,106);
  $f->{kestrel}      = red_or_green($record,106);

  $d->{hm_wx_sta}    = yes_or_no($record,107);
  $f->{hm_wx_sta}    = red_or_green($record,107);

  $d->{port_pkt}     = yes_or_no($record,108);
  $f->{port_pkt}     = red_or_green($record,108);

  $d->{pkt_sta_247}  = yes_or_no($record,109);
  $f->{pkt_sta_247}  = red_or_green($record,109);

  $d->{digi}         = yes_or_no($record,110);
  $f->{digi}         = red_or_green($record,110);

  $d->{pkt_node}     = yes_or_no($record,111);
  $f->{pkt_node}     = red_or_green($record,111);

  $d->{emerg_pwr}    = yes_or_no($record,112);
  $f->{emerg_pwr}    = red_or_green($record,112);

  $d->{fwd}          = yes_or_no($record,113);
  $f->{fwd}          = red_or_green($record,113);

  $d->{mag_mnt_ant}  = yes_or_no($record,114);
  $f->{mag_mnt_ant}  = red_or_green($record,114);

  $d->{db_mag_mnt}   = yes_or_no($record,115);
  $f->{db_mag_mnt}   = red_or_green($record,115);

  $d->{port_hf_ant}  = yes_or_no($record,116);
  $f->{port_hf_ant}  = red_or_green($record,116);

  $d->{port_gen}     = yes_or_no($record,117);
  $f->{port_gen}     = red_or_green($record,117);

  $d->{airmail}      = yes_or_no($record,118);
  $f->{airmail}      = red_or_green($record,118);

  $d->{id}           = yes_or_no($record,125);
  $f->{id}           = red_or_green($record,125);

  $d->{cbi}          = yes_or_no($record,126);
  $f->{cbi}          = red_or_green($record,126);

  #-------------------------------------------------------------------
  # write row
  #
  $mws->write($r,$c,  $record->[9],       $left);  # callsign
  $mws->write($r,++$c,$record->[19],      $left);  # lastname
  $mws->write($r,++$c,$record->[17],      $left);  # firstname
  $mws->write($r,++$c,$record->[18],      $left);  # middlename
  $mws->write($r,++$c,$record->[10],      $left);  # license class
  $mws->write($r,++$c,$d->{id},           $f->{id});

  $mws->write($r,++$c,$d->{phone},        $left);
  $mws->write($r,++$c,$d->{email},        $left);
  $mws->write($r,++$c,$d->{sms},          $left);
  $mws->write($r,++$c,$d->{address},      $left);
  $mws->write($r,++$c,$record->[28],      $left);  # county
  $mws->write($r,++$c,$record->[30],      $left);  # district

  $mws->write($r,++$c,$d->{notify_emerg}, $f->{notify_emerg});
  $mws->write($r,++$c,$d->{notify_ares},  $f->{notify_ares});
  $mws->write($r,++$c,$d->{notify_exer},  $f->{notify_exer});
  $mws->write($r,++$c,$d->{notify_wx},    $f->{notify_wx});
  $mws->write($r,++$c,$d->{notify_pse},   $f->{notify_pse});

  $mws->write($r,++$c,$d->{avail_247},    $f->{avail_247});
  $mws->write($r,++$c,$d->{avail_mfd},    $f->{avail_mfd});
  $mws->write($r,++$c,$d->{avail_mfe},    $f->{avail_mfe});
  $mws->write($r,++$c,$d->{avail_ssd},    $f->{avail_ssd});
  $mws->write($r,++$c,$d->{avail_sse},    $f->{avail_sse});

  $mws->write($r,++$c,$d->{wxavail_247},  $f->{wxavail_247});
  $mws->write($r,++$c,$d->{wxavail_mfd},  $f->{wxavail_mfd});
  $mws->write($r,++$c,$d->{wxavail_mfe},  $f->{wxavail_mfe});
  $mws->write($r,++$c,$d->{wxavail_ssd},  $f->{wxavail_ssd});
  $mws->write($r,++$c,$d->{wxavail_sse},  $f->{wxavail_sse});

  $mws->write($r,++$c,$d->{ec001},        $f->{ec001});
  $mws->write($r,++$c,$d->{ec016},        $f->{ec016});
  $mws->write($r,++$c,$d->{ics100},       $f->{ics100});
  $mws->write($r,++$c,$d->{ics200},       $f->{ics200});
  $mws->write($r,++$c,$d->{ics700},       $f->{ics700});
  $mws->write($r,++$c,$d->{ics800},       $f->{ics800});

  $mws->write($r,++$c,$d->{oes},          $f->{oes});
  $mws->write($r,++$c,$d->{cpr},          $f->{cpr});
  $mws->write($r,++$c,$d->{meat},         $f->{meat});
  $mws->write($r,++$c,$d->{skywarn},      $f->{skywarn});
  $mws->write($r,++$c,$d->{milid},        $f->{milid});
  $mws->write($r,++$c,$d->{cbi},          $f->{cbi});

  $mws->write($r,++$c,$d->{vhf_fixed},    $f->{vhf_fixed});
  $mws->write($r,++$c,$d->{vhf_mobile},   $f->{vhf_mobile});
  $mws->write($r,++$c,$d->{uhf_fixed},    $f->{uhf_fixed});
  $mws->write($r,++$c,$d->{uhf_mobile},   $f->{uhf_mobile});
  $mws->write($r,++$c,$d->{ssb_2m},       $f->{ssb_2m});
  $mws->write($r,++$c,$d->{fm_220},       $f->{fm_220});

  $mws->write($r,++$c,$d->{hf_mobile},    $f->{hf_mobile});
  $mws->write($r,++$c,$d->{hf_portable},  $f->{hf_portable});

  $mws->write($r,++$c,$d->{dig_fixed},    $f->{dig_fixed});
  $mws->write($r,++$c,$d->{dig_portable}, $f->{dig_portable});
  $mws->write($r,++$c,$d->{pkt_sta_247},  $f->{pkt_sta_247});
  $mws->write($r,++$c,$d->{port_pkt},     $f->{port_pkt});
  $mws->write($r,++$c,$d->{digi},         $f->{digi});
  $mws->write($r,++$c,$d->{pkt_node},     $f->{pkt_node});
  $mws->write($r,++$c,$d->{airmail},      $f->{airmail});
  $mws->write($r,++$c,$d->{aprs},         $f->{aprs});
  $mws->write($r,++$c,$d->{atv},          $f->{atv});

  $mws->write($r,++$c,$d->{mag_mnt_ant},  $f->{mag_mnt_ant});
  $mws->write($r,++$c,$d->{db_mag_mnt},   $f->{db_mag_mnt});
  $mws->write($r,++$c,$d->{port_hf_ant},  $f->{port_hf_ant});

  $mws->write($r,++$c,$d->{hm_wx_sta},    $f->{hm_wx_sta});
  $mws->write($r,++$c,$d->{kestrel},      $f->{kestrel});
  $mws->write($r,++$c,$d->{gps},          $f->{gps});
  $mws->write($r,++$c,$d->{fwd},          $f->{fwd});

  $mws->write($r,++$c,$d->{port_gen},     $f->{port_gen});
  $mws->write($r,++$c,$d->{emerg_pwr},    $f->{emerg_pwr});

  $mws->write($r,++$c,$record->[3],       $left);  # last update
  $mws->write($r,++$c,$record->[2],       $left);  # initial registration

  return 1;
}

######################################################################

sub date_of {

  my $record = shift;
  my $bfield = shift;                   # boolean field
  my $dfield = shift;                   # date field

  if ( $record->[$bfield] )
    {
      return $record->[$dfield];
    }

  else
    {
      return 'no';
    }
}

######################################################################

sub yes_or_no {

  my $record = shift;
  my $field  = shift;

  if ( $record->[$field] )
    {
      return 'yes';
    }

  else
    {
      return 'no';
    }
}

######################################################################

sub red_or_green {

  my $record = shift;
  my $field  = shift;

  if ( $record->[$field] )
    {
      return $green;
    }

  else
    {
      return $red;
    }
}

######################################################################

sub get_phone {

  my $record = shift;
  my $string = q{};

  # phone 1
  if ($record->[31])
    {
      my $phone    = $record->[31];
      my $type     = $record->[32];
      my $unlisted = $record->[33];

      if ( $type )
	{
	  $phone .= " ($type) ";
	}

      if ( $unlisted )
	{
	  $phone .= "UNLISTED";
	}

      $string .= "1: $phone ";
    }

  # phone 2
  if ($record->[34])
    {
      my $phone    = $record->[34];
      my $type     = $record->[35];
      my $unlisted = $record->[36];

      if ( $type )
	{
	  $phone .= " ($type) ";
	}

      if ( $unlisted )
	{
	  $phone .= "UNLISTED";
	}

      $string .= "2: $phone ";
    }

  # phone 3
  if ($record->[37])
    {
      my $phone    = $record->[37];
      my $type     = $record->[38];
      my $unlisted = $record->[39];

      if ( $type )
	{
	  $phone .= " ($type) ";
	}

      if ( $unlisted )
	{
	  $phone .= "UNLISTED";
	}

      $string .= "3: $phone";
    }

  return $string;
}

######################################################################

sub get_email {

  my $record = shift;
  my $string = q{};

  # email 1
  if ($record->[42])
    {
      my $email    = $record->[42];
      my $type     = $record->[43];
      my $unlisted = $record->[44];

      if ( $type )
	{
	  $email .= " ($type) ";
	}

      if ( $unlisted )
	{
	  $email .= "UNLISTED";
	}

      $string .= "1: $email ";
    }

  # email 2
  if ($record->[45])
    {
      my $email    = $record->[45];
      my $type     = $record->[46];
      my $unlisted = $record->[47];

      if ( $type )
	{
	  $email .= " ($type) ";
	}

      if ( $unlisted )
	{
	  $email .= "UNLISTED";
	}

      $string .= "2: $email ";
    }

  # email 3
  if ($record->[48])
    {
      my $email    = $record->[48];
      my $type     = $record->[49];
      my $unlisted = $record->[50];

      if ( $type )
	{
	  $email .= " ($type) ";
	}

      if ( $unlisted )
	{
	  $email .= "UNLISTED";
	}

      $string .= "3: $email";
    }

  return $string;
}

######################################################################

sub get_sms {

  my $record = shift;

  if ($record->[40] and $record->[41])
    {
      my $phone   = $record->[40];
      my $carrier = $record->[41];

      $phone =~ s/[\)\(\-\ ]//g;

      return "$phone$carrier";
    }

  else
    {
      return q{};
    }
}

######################################################################

sub get_address {

  my $record = shift;
  my $string = q{};

  if ($record->[23])
    {
      $string .= "$record->[23] ";      # address 1
    }

  if ($record->[24])
    {
      $string .= "$record->[24] ";      # address 2
    }

  if ($record->[25])
    {
      $string .= "$record->[25] ";      # city
    }

  if ($record->[26])
    {
      $string .= "$record->[26] ";      # state
    }

  if ($record->[27])
    {
      $string .= "$record->[27]";      # zipcode
    }

  if ($record->[29])
    {
      $string .= " (near $record->[29])"; # crossstreet
    }

  return $string;
}

######################################################################

sub get_member_list {

  if ( $order )
    {
      return [ sort by_order @{ $data } ];
    }

  else
    {
      return $data;
    }
}

######################################################################

sub read_csv_file {

  my $file = shift;

  my $data = [];
  my $line = 0;

  open my $fh, "< :encoding(UTF-8)", $file or die "$file not readable";
  while (<$fh>) {
    ++ $line;
    next if $skip_line{$line};
    my $csv    = Text::CSV->new();
    my $status = $csv->parse($_);
    my $record = [ $csv->fields() ];
    push( @{$data}, $record );
  }
  close $fh;

  return $data;
}

######################################################################

sub by_order {
  lc($a->[$order - 1]) cmp lc($b->[$order - 1]);
}

######################################################################

1;
